package database;

import entity.FTodo;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHelper extends SQLiteOpenHelper {
	public static final String TAG = DatabaseHelper.class.getName();
	private static DatabaseHelper instance = null;
	private SQLiteDatabase db;

	public static DatabaseHelper getInstance(Context context) {
		if (instance == null) {
			instance = new DatabaseHelper(context);
		}

		return instance;
	}

	public Cursor getAllFTodo() throws SQLException {
		return db.rawQuery(String.format("Select * from %s", Config.TABLE_NAME), null);
	}
	
	public Cursor getListDate(int dateFrom, int dateTo) throws SQLException {
		return db.rawQuery(String.format(
				"Select %s from %s where %s >= %d and %s <= %d group by %s",
				Config.COL_DATE, Config.TABLE_NAME, Config.COL_DATE, dateFrom,
				Config.COL_DATE, dateTo, Config.COL_DATE), null);
	}
	
	public Cursor getListByTaskName(String taskName, int date) throws SQLException {
		return db.rawQuery("Select * from " + Config.TABLE_NAME + " where " + Config.COL_DATE + " >= " + date + " and " + Config.COL_TASKNAME + " like '%" + taskName + "%'", null);
	}
	
	public Cursor getListFTodoByDate(int date) throws SQLException {
		return db.rawQuery(String.format("Select * from %s where %s = %d",
				Config.TABLE_NAME, Config.COL_DATE, date), null);
	}

	public boolean deleteFtodo(int rowId) {
		return db.delete(Config.TABLE_NAME, Config.COL_ID + " = " + rowId, null) > 0;
	}

	public boolean insertFTodo(FTodo ftodo) {
		ContentValues value = new ContentValues();
		value.put(Config.COL_TASKNAME, ftodo.taskname);
		value.put(Config.COL_ISFINISH, ftodo.isFinish);
		value.put(Config.COL_PRIORITY, ftodo.priority);
		value.put(Config.COL_DATE, ftodo.dateCreate);
		value.put(Config.COL_TIME, ftodo.timeCreate);
		value.put(Config.COL_REMINDER, ftodo.reminder);
		value.put(Config.COL_PLACE, ftodo.place);
		value.put(Config.COL_PEOPLE, ftodo.people);

		return db.insert(Config.TABLE_NAME, null, value) > 0;
	}

	public boolean updateFTodo(FTodo ftodo) {
		ContentValues value = new ContentValues();
		value.put(Config.COL_TASKNAME, ftodo.taskname);
		value.put(Config.COL_DATE, ftodo.dateCreate);
		value.put(Config.COL_TIME, ftodo.timeCreate);
		value.put(Config.COL_ISFINISH, ftodo.isFinish);
		value.put(Config.COL_PEOPLE, ftodo.people);
		value.put(Config.COL_PLACE, ftodo.place);
		value.put(Config.COL_REMINDER, ftodo.reminder);
		value.put(Config.COL_PRIORITY, ftodo.priority);

		return db.update(Config.TABLE_NAME, value, Config.COL_ID + " = "
				+ ftodo.id, null) > 0;
	}

	public void close() {
		if (db != null) {
			db.close();
		}
	}

	private DatabaseHelper(Context context) {
		super(context, Config.DATABASE_NAME, null, Config.DATABASE_VERSION);
		db = this.getWritableDatabase();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(Config.DATABASE_CREATE);
		Log.d(TAG, Config.DATABASE_CREATE);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
				+ newVersion + ", which will destroy all olddata");
		db.execSQL("DROP TABLE IF EXISTS " + Config.TABLE_NAME);
		onCreate(db);
	}

}
